/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuitionmanagement.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JComboBox;
import tuitionmanagement.entity.OrderEntity;
import tuitionmanagement.util.DBUtil;

/**
 *
 * @author XUAN
 */
public class OrderDao {

    private Connection con;
    private ResultSet rs;
    private PreparedStatement pst;
    OrderEntity order = new OrderEntity();
    private String lastError;
    private final String sql_SelectByDate = "select * from [Order] where FeeDate between ? and ? ";
    private final String sql_SelectByInvoiceId = "select * from [Order] where InvoiceId =?";
    private final String sql_SelectByOrderId = "select * from [Order] where InvoiceId =? and OrderId=?";
    private final String sql_selectOrder = "select * from [Order]";
    private final String sql_selectOrderId = "select OrderId from [Order] where InvoiceId=?";
    private final String sql_insertOrder = "insert into [Order] values(?,?,?,?)";
    private final String sql_updateOrder = "update [Order] set InvoiceId =?, [Money] = ?, FeeDate = ?,RateId =? where OrderId = ?";
    private final String sql_deleteOrder = "delete [Order] where OrderId = ?";
    private final String sql_selectOrderByStudentAndClass = "SELECT Class.ClassId, Student.StudentId, [Order].FeeDate, [Order].Money "
            + " FROM [Order] INNER JOIN "
            + " Invoice ON [Order].InvoiceId = Invoice.InvoiceId INNER JOIN"
            + " Student ON Invoice.StudentId = Student.StudentId INNER JOIN"
            + " Student_Class ON Student.StudentId = Student_Class.StudentId INNER JOIN"
            + " Class ON Student_Class.ClassId = Class.ClassId"
            + " where Student.StudentId = ? AND Class.ClassId = ?";

    public ArrayList<OrderEntity> getOrderByDate(Date searchFrom, Date searchTo) {
        ArrayList<OrderEntity> list = new ArrayList<OrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_SelectByDate);
            pst.setDate(1, new java.sql.Date(searchFrom.getTime()));
            pst.setDate(2, new java.sql.Date(searchTo.getTime()));
            rs = pst.executeQuery();
            while (rs.next()) {
                OrderEntity order = new OrderEntity();
                order.setOrderId(rs.getInt("OrderId"));
                order.setInvoiceId(rs.getInt("InvoiceId"));
                order.setMoney(rs.getFloat("Money"));
                order.setFeeDate(rs.getDate("FeeDate"));
                order.setRateId(rs.getInt("RateId"));

                list.add(order);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }



    public ArrayList<OrderEntity> getTableOrder(int invoiceId) {
        ArrayList<OrderEntity> list = new ArrayList<OrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_SelectByInvoiceId);
            pst.setInt(1, invoiceId);

            rs = pst.executeQuery();
            while (rs.next()) {
                OrderEntity order = new OrderEntity();
                order.setOrderId(rs.getInt("OrderId"));
                order.setInvoiceId(rs.getInt("InvoiceId"));
                order.setMoney(rs.getFloat("Money"));
                order.setFeeDate(rs.getDate("FeeDate"));
                order.setRateId(rs.getInt("RateId"));

                list.add(order);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

     public ArrayList<OrderEntity> getTableOrderByOrderId(int invoiceId, int orderId) {
        ArrayList<OrderEntity> list = new ArrayList<OrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_SelectByOrderId);
            pst.setInt(1, invoiceId);
            pst.setInt(2, orderId);
            rs = pst.executeQuery();
            while (rs.next()) {
                OrderEntity or = new OrderEntity();
                or.setOrderId(rs.getInt("OrderId"));
                or.setInvoiceId(rs.getInt("InvoiceId"));
                or.setMoney(rs.getFloat("Money"));
                or.setFeeDate(rs.getDate("FeeDate"));
                or.setRateId(rs.getInt("RateId"));

                list.add(or);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }

    public void getcmbOrderId(JComboBox cmbOrderId, String orderId, String invoiceId) {

        Vector vt = new Vector();
        vt.add("");
        CallableStatement cs = null;
        ResultSet rst = null;
        try {
            con = DBUtil.getConn();
            cs = con.prepareCall(sql_selectOrderId);
            cs.setInt(1,Integer.parseInt(invoiceId));
            rst = cs.executeQuery();
            if (rst == null) {
                System.out.println("Not Data");
            } else {
                while (rst.next()) {
//                    vt.add(rst.getString(OrderId));
                    vt.add(rst.getString(orderId));
                }
            }
            cmbOrderId.setModel(new DefaultComboBoxModel(vt));
            DBUtil.closeConn(con);
        } catch (Exception ex) {
            System.out.println("Error Add, GetCmbOrderId " + ex.getMessage());
        }
    }

    public ArrayList<OrderEntity> readByAll() {
        ArrayList<OrderEntity> list = new ArrayList<OrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectOrder);
            rs = pst.executeQuery();
            while (rs.next()) {
                OrderEntity f = new OrderEntity();
                f.setOrderId(rs.getInt("OrderId"));
                f.setInvoiceId(rs.getInt("InvoiceId"));
                f.setMoney(rs.getFloat("Money"));
                f.setFeeDate(rs.getDate("FeeDate"));
                f.setRateId(rs.getInt("RateId"));

                list.add(f);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public ArrayList<OrderEntity> readByStudentAndClass(int studentId, int classId) {
        ArrayList<OrderEntity> list = new ArrayList<OrderEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectOrderByStudentAndClass);
            pst.setInt(1, studentId);
            pst.setInt(2, classId);
            rs = pst.executeQuery();
            while (rs.next()) {
                OrderEntity f = new OrderEntity();
                f.setMoney(rs.getFloat("Money"));
                f.setFeeDate(rs.getDate("FeeDate"));
                list.add(f);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public boolean addOrder(OrderEntity order) {
        if (!order.validate()) {
            setLastError(order.getLastError());
            return false;
        }
        try {
            con = DBUtil.getConn();
            pst = con.prepareStatement(sql_insertOrder);
            pst.setFloat(1, order.getInvoiceId());
            pst.setFloat(2, order.getMoney());
            pst.setDate(3, new java.sql.Date(order.getFeeDate().getTime()));
            pst.setInt(4, order.getRateId());

            if (pst.executeUpdate() > 0) {
                setLastError("Add Rate's information successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    public boolean update(OrderEntity or) {
        con = DBUtil.getConn();

        try {
            pst = con.prepareStatement(sql_updateOrder, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            pst.setInt(1, or.getInvoiceId());
            pst.setFloat(2, or.getMoney());
            pst.setDate(3, new java.sql.Date(or.getFeeDate().getTime()));
            pst.setInt(4, or.getRateId());
            pst.setInt(5, or.getOrderId());
            pst.executeUpdate();
            //rs.first();
            //rs.updateDate(1,(Date) rate.getDateRate());
            //rs.updateFloat(2,rate.getUsd());
            //rs.updateFloat(3,rate.getVnd());
            //rs.updateRow();
            con.close();
            setLastError("Update FeeLevel successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    public boolean deleteOrder(int OrderId) {
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_deleteOrder, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, OrderId);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete FeeLevel successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }



}
